package csc.nndp.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


public class EmDBCreator {
	
	private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	  private String protocol = "jdbc:derby:";
	  private String username = "admin";
	  private String password = "admin123";
	  private String dbName = "jspservlet";
	  private String tableName = "userdb";
	  private Properties userInfo;
	  
	  
	  
	  public EmDBCreator() {
		  
	    userInfo = new Properties();
	    userInfo.put("user", username);
	    userInfo.put("password", password);
	  }
	  
	  
	  
	  public void createDatabase() throws Exception {

		  User[] users = {
				  new User(1,"admin", "admin123","Nhut", "Nguyen", "0972180308","toannhut18@gmail.com", "admin"),
				  new User(2,"user", "P@ssword123","Phu", "Phu", "0972180308","Phu@gmail.com", "user"),
				  new User(3,"user", "P@ssword123","Ngan", "Ngan", "0972180308","Ngan@gmail.com", "user"),
				  new User(4,"user", "P@ssword123","Duc", "Duc", "0972180308","Duc@gmail.com", "user")
		  };
	    try {
	    	Class.forName(driver);
	      String dbUrl = protocol + dbName + ";create=true";
	      Connection connection = 
	        DriverManager.getConnection(dbUrl, userInfo);
	      Statement statement = connection.createStatement();
	      String format = "VARCHAR(20)";
	      String tableDescription = 
	        String.format
	          ("CREATE TABLE %s" +
	              "(userId INT, userName %s, password %s," + " firstName %s,lastName %s,phone %s, email %s, role %s)",
	        	tableName, format, format,format,format, format, format,format, format);
	      statement.execute(tableDescription);
	      
	      String template =
	        String.format("INSERT INTO %s VALUES(?, ?, ?, ?, ?,?,?,?)",
	                      tableName);
	      PreparedStatement inserter = 
	    	        connection.prepareStatement(template);
	      for(User e: users) {
	    	  inserter.setInt(1, e.getUserId());
	    	  inserter.setString(2, e.getUserName());
	    	  inserter.setString(3, e.getPassword());
	    	  inserter.setString(4, e.getFirstName());
	    	  inserter.setString(5, e.getLastName());
	    	  inserter.setString(6, e.getPhone());
	    	  inserter.setString(7, e.getEmail());
	    	  inserter.setString(8, e.getRole());
	    	  
	    	  
	    	  inserter.executeUpdate();
	          System.out.printf("Inserted %s %s.%n", 
	                            e.getFirstName(), 
	                            e.getLastName());
	      }
	      inserter.close();
	      connection.close();
	    } catch (SQLException sqle) {
	      // If table already exists, then skip everything else
	    	//sqle.printStackTrace();
	    }
	  }
	  
	  public void showTable() {
		    try {
		      String dbUrl = protocol + dbName;
		      Connection connection = DriverManager.getConnection(dbUrl, userInfo);
		      Statement statement = connection.createStatement();
		      String query = String.format("SELECT * FROM %s", tableName);
		      ResultSet resultSet = statement.executeQuery(query);
		      while(resultSet.next()) {
		        int id = resultSet.getInt("userId");
		        String firstName = resultSet.getString("firstName");
		        String lastName = resultSet.getString("lastName");
		        String username = resultSet.getString("userName");
		        //	String email= resultSet.getString("email");
		        
		        /*System.out.printf("%s - %s - %s - %n - %s \n",
		                          firstName, lastName, id,username);*/
		      }
		      connection.close();
		    } catch (SQLException sqle) {
		      sqle.printStackTrace();
		    }  
		  }
		  
	  public static void main(String[] args) throws Exception {
		    EmDBCreator tester = new EmDBCreator();
		    tester.createDatabase();
		    tester.showTable();
		    
		  }

}
